﻿CREATE VIEW [dbo].[vw_AggregatedFaults] AS

SELECT 		
		COUNT([FaultID]) AS FaultCount,
		[Application],
		[ServiceName],
		[ErrorType],
		[ExceptionType],
		[FaultCode],
		[FailureCategory],
		[FaultSeverity],
		YEAR([DateTime]) AS [Year],
		MONTH([DateTime]) AS [Month],
		DAY([DateTime]) AS [Day],
		DATEPART(hh, [DateTime]) AS [Hour],
		CONVERT(datetime, (CONVERT(varchar(4), YEAR([DateTime])) + '/' + CONVERT(varchar(2), MONTH([DateTime])) + '/' + CONVERT(varchar(2), DAY([DateTime])) + ' ' + CONVERT(varchar(2), DATEPART(hh, [DateTime])) + ':00:00')) as [DateTime],
		[DateTime] AS [TrueDateTime]

FROM Fault

GROUP BY 
		[Application],
		[ServiceName],
		[ErrorType],
		[ExceptionType],
		[FaultCode],
		[FailureCategory],
		[FaultSeverity],
		YEAR([DateTime]),
		MONTH([DateTime]),
		DAY([DateTime]),
		DATEPART(hh, [DateTime]),
		[DateTime]
